package com.zgm.ch09.jdbc.jdbcTemplate.execute._4_1;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.LinkedList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.StatementCallback;

public class JdbcTemplateDemo {

	private JdbcTemplate jdbcTemplate;
	

	public static void main(String[] args) {
//		ApplicationContext ac = new ClassPathXmlApplicationContext("com/zgm/ch09/jdbc/jdbcTemplate/execute/_4_1/_4_1-context.xml",
//									JdbcTemplateDemo.class);  //会报错
//		ApplicationContext ac = new ClassPathXmlApplicationContext("com/zgm/ch09/jdbc/jdbcTemplate/execute/_4_1/_4_1-context.xml");//可行
		ApplicationContext ac = new ClassPathXmlApplicationContext("_4_1-context.xml", JdbcTemplateDemo.class);
		
		JdbcTemplateDemo demo = (JdbcTemplateDemo) ac.getBean("jdbcTemplateDemo");
		demo.run();
	}
	
	private void run() {
		this.jdbcTemplate.execute(new ConnectionCallback() {
			public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
				PreparedStatement createTable = connection.prepareStatement(
							"create table t_x (id number(19, 0) not null, constraint pk_x primary key(id))"
						);
				createTable.execute();
				
				return null;
			}
		});
		
		this.jdbcTemplate.execute(new StatementCallback() {
			public Object doInStatement(Statement statement) throws SQLException, DataAccessException {
				return statement.execute("insert into t_x (id) values (1)");
			}
		});
		
		this.jdbcTemplate.execute("insert into t_x (id) values (2)");
		
		List<Long> ids;
		
		ids = (List<Long>) this.jdbcTemplate.execute(new MyPreparedStatementCreator(), new MyPreparedStatementCallback());
		System.out.println("ids 值为：" + ids);
		
		ids = (List<Long>) this.jdbcTemplate.execute("select id from t_x", new MyPreparedStatementCallback());
		System.out.println("ids2 值为：" + ids);
		
		System.out.println(this.jdbcTemplate.execute(new MyCallableStatementCreator(), new MyCallableStatementCallback()));
		
		this.jdbcTemplate.execute("{call p_actstartled(42)}", 
			new CallableStatementCallback(){
				public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
					cs.execute();
					
					return null;
				}
		});
		
		this.jdbcTemplate.execute("drop table t_x");
	}
	
	private static class MyPreparedStatementCreator implements PreparedStatementCreator {

		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement ps = connection.prepareStatement("select * from t_x where id=?");
			ps.setLong(1, 1L);
//			ps.setLong(1, 1);
			
			return ps;
		}
		
	}
	
	private static class MyPreparedStatementCallback implements PreparedStatementCallback {
		public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
			ResultSet rs = ps.executeQuery();
			
			List<Long> ids = new LinkedList<Long>();
			
			while(rs.next()) {
				ids.add(rs.getLong(1));
			}
			
			return ids;
		}
	}
	
	private static class MyCallableStatementCreator implements CallableStatementCreator {
		
		public CallableStatement createCallableStatement(Connection connection) throws SQLException {
			CallableStatement cs = connection.prepareCall("{? = call f_calculate}");
			cs.registerOutParameter(1, Types.INTEGER);
			
			return cs;
		}
	}
	
	private static class MyCallableStatementCallback implements CallableStatementCallback {
		public Object doInCallableStatement(CallableStatement cs) throws SQLException {
			cs.execute();
			
			return cs.getLong(1);
		}
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
